package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DomainLayer.*;

public class DBJournal implements IFDBJournal {

	private Connection con;
	private DBLabScheme dbLabScheme;
	private DBTemperatureScheme dbTempScheme;
	private DBNursingDocumentation dbNursingDoc;

	public DBJournal() {
		con = DbConnection1.getInstance().getDBcon();
		dbLabScheme = new DBLabScheme();
		dbTempScheme = new DBTemperatureScheme();
		dbNursingDoc = new DBNursingDocumentation();
	}

	// @Override

	private ArrayList<Journal> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Journal> list = new ArrayList<Journal>();

		String query = buildQuery(wClause);
		System.out.println("Query= " + query);

		try { // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Journal journal = new Journal();
				journal = buildJournal(results);
				list.add(journal);
			}// end while
			stmt.close();

		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;

	}

	private Journal singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Journal journal = new Journal();

		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				journal = buildJournal(results);
				stmt.close();
				if (retrieveAssociation) {
					try {
						journal.setLabscheme(dbLabScheme
								.getLabSchemeForJournal(journal));
					} catch (Exception ex) {

					}

					try {
						journal.setTempScheme(dbTempScheme
								.getTemperatureSchemeForJournal(journal));
					} catch (Exception ex) {

					}

					journal.setNursDoc(dbNursingDoc
							.getNursingDocumentationForJournal(journal));

				}

			} else {
				journal = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return journal;

	}

	private String buildQuery(String wClause) {
		String query = " Select * from Journal ";
		if (wClause.length() > 0)
			query = query + " WHERE" + wClause;

		return query;
	}

	private Journal buildJournal(ResultSet results) {
		Journal journalObj = new Journal();
		try { // the columns from the table Journal are used

			journalObj.setName(results.getString("name"));
			journalObj.setSex(results.getString("sex"));
			journalObj.setAge(results.getInt("age"));
			journalObj.setHosp_date(results.getString("hosp_date"));
			journalObj.setRelatives(results.getString("relatives"));
			journalObj.setContact_causes(results.getString("contactCauses"));
			journalObj.setHypersensitivity(results
					.getString("hypersensitivity"));
			journalObj.setMedical_diagnosis(results
					.getString("medical_diagnosis"));
			journalObj.setTheme(results.getString("theme"));
			journalObj.setModule(results.getString("module"));
			journalObj.setNutrisk(new NutritionRiskScheme(results
					.getString("NutRiskName")));
			journalObj.setBscale(new BradenScale(results
					.getString("bscaleName")));

		} catch (Exception e) {
			System.out.println("error in building the journal");
		}

		return journalObj;
	}

	@Override
	public int createJournal(Journal journal)  {
		int rc = -1;

		String query = "INSERT INTO journal VALUES('" + journal.getSex() + "',"
				+ journal.getAge() + ",'" + journal.getHosp_date() + "','"
				+ journal.getRelatives() + "','" + journal.getContact_causes()
				+ "','" + journal.getHypersensitivity() + "','"
				+ journal.getMedical_diagnosis() + "','" + journal.getName()
				+ "','" + journal.getTheme() + "','" + journal.getModule();
		
		if(journal.getBscale()!=null&&journal.getNutrisk()!=null){
				query+= "','" + journal.getBscale().getName() + "','"
				+ journal.getNutrisk().getName() + "')";
		}
		else
		{
			if(journal.getBscale()!=null){
				query+= "','" + journal.getBscale().getName()+"','" +"null')";
			}
			else
			{
			if(journal.getNutrisk()!=null){
				query+= "','" + journal.getNutrisk().getName()+"','" +"null')";
			}
			else
			{
				query+= "','"+"null"+"','" +"null')";
			}
			}
			
		}

		System.out.println("insert : " + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		} catch (SQLException ex) {
			System.out.println("journal does not exist");
			
		}

		return rc;
	}

	@Override
	public int deleteJournal(Journal journal) {
		int rc = -1;

		String query = "DELETE FROM Journal WHERE name ='" + journal.getName()
				+ "' and contactCauses='" + journal.getContact_causes() + "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in sales order db: " + ex);
		}
		return (rc);
	}

	@Override
	public int updateJournal(Journal journalObject) {
		Journal journal = journalObject;
		int rc = -1;

		String query = "UPDATE Journal SET " + "sex = '" + journal.getSex()
				+ "'," + "age= " + journal.getAge() + "," + "hosp_date= '"
				+ journal.getHosp_date() + "'," + "relatives= '"
				+ journal.getRelatives() + "',contactCauses='"
				+ journal.getContact_causes() + "'," + "hypersensitivity= '"
				+ journal.getHypersensitivity() + "'," + "medical_diagnosis= '"
				+ journal.getMedical_diagnosis() + "'," + " theme='"
				+ journal.getTheme() + "'," + "module= '" + journal.getModule()
				+ "'," + "NutRiskName='" + journal.getNutrisk().getName()
				+ "'," + "bscaleName='" + journal.getBscale().getName() +

				"' WHERE name ='" + journal.getName() + "' and contactCauses='"
				+ journal.getContact_causes() + "'";

		System.out.println("Update query:" + query);
		try { // update product
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in journal db: " + ex);
		}
		return (rc);

	}

	@Override
	public Journal findJournal(String name, String contactCouses,
			boolean retrieveAssociation) {
		String wClause = " name like '" + name + "' and contactCauses like '"
				+ contactCouses + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public ArrayList<Journal> getAllJournals(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public ArrayList<Journal> getJournals(String theme, String module,
			boolean retrieveAssociation) {
		return miscWhere(" module like '" + module + "' and theme like '"
				+ theme + "'", retrieveAssociation);
	}

	public int assignToBed(Journal journal, int roomNo, int bedNo, String date) {
		int rc = 0;
		String query = "Insert into Journal_Bed VALUES ('" + journal.getName()
				+ "','" + journal.getContact_causes() + "'," + bedNo + ","
				+ roomNo + ",'" + date + "')";
		System.out.println("Insert " + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		} catch (SQLException ex) {
			System.out.println("problem while assigning to bed");

		}

		return rc;
	}

	@Override
	public ArrayList<String> findAllThemes(String moduleName,
			boolean retrieveAssociation) {
		ArrayList<String> list = new ArrayList<String>();
		String wClause = " module='" + moduleName + "'";

		for (Journal journal : miscWhere(wClause, true)) {

			if (!list.contains(journal.getTheme())) {
				list.add(journal.getTheme());

			}

		}

		return list;

	}

	public Journal findJournalAccordingBed(int bed_No, int room_No, String date) {

		String query = "Select * From Journal_Bed where bed_no='" + bed_No
				+ "' and room_No='" + room_No + "' and date='" + date + "'";
		System.out.println(query);
		Journal journal = null;

		ResultSet results;

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {

				String name = results.getString(1);
				String contact = results.getString(2);
				journal = findJournal(name, contact, true);

				stmt.close();
			}

		}

		catch (Exception ex) {
			ex.printStackTrace();
		}

		return journal;

	}

	public void connectJournaLabScheme(Journal journal) {
		if (journal.getLabscheme() != null) {
			
			for (int i = 0; i < journal.getLabscheme().size(); i++) {
				String query = "Insert into LabSchemeJournal values ('"
						+ journal.getName() + "','"
						+ journal.getContact_causes() + "','"
						+ journal.getLabscheme().get(i).getName() + "','"
						+ journal.getLabscheme().get(i).getDate() + "')";
				System.out.println(query);

				Statement stmt;
				try {
					 stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					stmt.executeUpdate(query);
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
		}
		}}
	
	
	
	public void connectJournalTemperatureScheme(Journal journal)
	{
		if(journal.getTempScheme()!=null)
		for(int i=0;i<journal.getTempScheme().size();i++)
		{
			String query="Insert into TempScheme values ('"+journal.getTempScheme().get(i).getNameScheme()+"','"+journal.getName()+"','"+journal.getTempScheme().get(i).getDatetime()+"','"+journal.getContact_causes()+"')";
		      System.out.println(query);
		      
				Statement stmt;
				try {
					stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					
					stmt.executeUpdate(query);
					stmt.close();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	
	
	
	public void connectJournalDocumentation(Journal journal)
	{
		if(journal.getNursDoc()!=null)
		for(int i=0;i<journal.getNursDoc().size();i++)
		{
			String query="Insert into NursingDocJournal values ('"+journal.getName()+"','"+journal.getContact_causes()+"','"+journal.getNursDoc().get(i).getName()+"','"+journal.getNursDoc().get(i).getDate()+"')";
		      System.out.println(query);
		      
				Statement stmt;
				try {
					stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					
					stmt.executeUpdate(query);
					stmt.close();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		
		}
		}
	
	

	

}
